Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace sk.DAL.SQLProviders
    Public Class SQLCommentProvider
        Inherits CommentProvider


        Public Overrides Function ChangeCommentApproval(ByVal CommentID As Integer, ByVal status As Boolean) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("ChangeCommentApproval", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = CommentID
                    Dim pStatus As New SqlParameter("@Approve", SqlDbType.Bit)
                    pStatus.Value = status
                    cmd.Parameters.Add(pCommentID)
                    cmd.Parameters.Add(pStatus)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function CreateComment(ByVal Cmt As Comment) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("CreateComment", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = Cmt.CommentID
                    Dim pUserID As New SqlParameter("@UserID", SqlDbType.VarChar)
                    pUserID.Value = Cmt.UserID
                    Dim pDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pDate.Value = Cmt.CommDate
                    Dim pText As New SqlParameter("@Text", SqlDbType.VarChar)
                    pText.Value = Cmt.Text
                    Dim pApproved As New SqlParameter("@Approved", SqlDbType.Bit)
                    pApproved.Value = Cmt.Approved
                    Dim pArticleID As New SqlParameter("@ArticleID", SqlDbType.SmallInt)
                    pArticleID.Value = Cmt.ArticleID

                    cmd.Parameters.Add(pCommentID)
                    cmd.Parameters.Add(pUserID)
                    cmd.Parameters.Add(pDate)
                    cmd.Parameters.Add(pText)
                    cmd.Parameters.Add(pApproved)
                    cmd.Parameters.Add(pArticleID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function DeleteComment(ByVal CmtID As Integer) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("DeleteComment", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = CmtID
                    cmd.Parameters.Add(pCommentID)


                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetAllComments() As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetComment", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function GetCommentByID(ByVal CmtID As Integer) As Comment
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetCommentByID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = CmtID
                    cmd.Parameters.Add(pCommentID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentFromReader(reader)
                End Using
            Catch ex As Exception
                Return New Comment
            End Try
        End Function

        Public Overrides Function GetCommentsByUserID(ByVal UserID As String) As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetCommentsByUserID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pUserID As New SqlParameter("@ID", SqlDbType.VarChar)
                    pUserID.Value = UserID
                    cmd.Parameters.Add(pUserID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)
                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function GetCommentsForArticle(ByVal ArticleID As Integer) As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetCommentsForArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    cmd.Parameters.Add(pArticleID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)
                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function GetApprovedCommentsForArticle(ByVal ArticleID As Integer) As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetApprovedCommentsForArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    cmd.Parameters.Add(pArticleID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)
                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function GetCommentsOnDate(ByVal CommentDate As Date) As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetCommentsOnDate", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pDate.Value = CommentDate
                    cmd.Parameters.Add(pDate)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)
                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function GetUserCommentsForArticle(ByVal userID As String, ByVal ArticleID As Integer) As System.Collections.Generic.List(Of Comment)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("GetUserCommentForArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pUserID As New SqlParameter("@UserID", SqlDbType.VarChar)
                    pUserID.Value = userID
                    Dim pArticleID As New SqlParameter("@ArticleID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    cmd.Parameters.Add(pUserID)
                    cmd.Parameters.Add(pArticleID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetCommentCollectionFromReader(reader)
                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Comment)
            End Try
        End Function

        Public Overrides Function UpdateComment(ByVal Cmt As Comment) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("UpdateComment", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pCommentID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pCommentID.Value = Cmt.CommentID
                    Dim pUserID As New SqlParameter("@UserID", SqlDbType.VarChar)
                    pUserID.Value = Cmt.UserID
                    Dim pDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pDate.Value = Cmt.CommDate
                    Dim pText As New SqlParameter("@Text", SqlDbType.VarChar)
                    pText.Value = Cmt.Text
                    Dim pApproved As New SqlParameter("@Approved", SqlDbType.Binary)
                    pApproved.Value = Cmt.Approved
                    Dim pArticleID As New SqlParameter("@ArticleID", SqlDbType.SmallInt)
                    pArticleID.Value = Cmt.ArticleID

                    cmd.Parameters.Add(pCommentID)
                    cmd.Parameters.Add(pUserID)
                    cmd.Parameters.Add(pDate)
                    cmd.Parameters.Add(pText)
                    cmd.Parameters.Add(pApproved)
                    cmd.Parameters.Add(pArticleID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetNextID() As Integer
            Try
                Using con As New SqlConnection(Me.ConnectionString)
                    Dim cmd As New SqlCommand("GetNextID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()
                    Dim dr As SqlDataReader
                    dr = cmd.ExecuteReader
                    Dim newId As Integer
                    dr.Read()
                    newId = CInt(dr.GetValue(0))
                    Return newId + 1
                End Using
            Catch ex As Exception
                Return 0
            End Try
        End Function
    End Class
End Namespace